package com.tangqihang229350526.mynote.database

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

class NoteDatabase(context: Context?) :
    SQLiteOpenHelper(context, DB_NAME, null, V) {

    init {
        this.writableDatabase.execSQL(
            "create table IF NOT EXISTS " + NOTE_TABLE_NAME
                    + "(id integer primary key autoincrement, title varchar, time varchar, address varchar, content varchar, type varchar, image varchar)"
        )
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(
            "create table " + NOTE_TABLE_NAME
                    + "(id integer primary key autoincrement, title varchar, time varchar, address varchar, content varchar, type varchar, image varchar)"
        )
    }


    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {}

    fun insertNote(
        title: String?,
        time: String?,
        address: String?,
        content: String?,
        type: String?,
        image: String?
    ): Long {
        val database = this.writableDatabase
        val values = ContentValues()
        values.put("title", title)
        values.put("time", time)
        values.put("address", address)
        values.put("content", content)
        values.put("type", type)
        values.put("image", image)
        return database.insert(NOTE_TABLE_NAME, null, values)
    }

    fun query(key: String): List<Note> {
        val list: MutableList<Note> = ArrayList()
        val database = this.writableDatabase

        // 如果 key 是空字符串，则只查询所有记录
        if (key.isEmpty()) {
            val cursor = database.query(NOTE_TABLE_NAME, null, null, null, null, null, null)
            while (cursor.moveToNext()) {
                @SuppressLint("Range") val id = cursor.getInt(cursor.getColumnIndex("id"))
                @SuppressLint("Range") val title = cursor.getString(cursor.getColumnIndex("title"))
                @SuppressLint("Range") val time = cursor.getString(cursor.getColumnIndex("time"))
                @SuppressLint("Range") val address = cursor.getString(cursor.getColumnIndex("address"))
                @SuppressLint("Range") val content = cursor.getString(cursor.getColumnIndex("content"))
                @SuppressLint("Range") val type = cursor.getString(cursor.getColumnIndex("type"))
                @SuppressLint("Range") val image = cursor.getString(cursor.getColumnIndex("image"))
                list.add(Note(id, title, time, address, content, type, image))
            }
            cursor.close()
            return list
        }

        // 否则，执行模糊查询
        val cursor = database.query(
            NOTE_TABLE_NAME, null,
            "content like '%$key%' or time like '%$key%'", null, null, null, null
        )
        while (cursor.moveToNext()) {
            @SuppressLint("Range") val id = cursor.getInt(cursor.getColumnIndex("id"))
            @SuppressLint("Range") val title = cursor.getString(cursor.getColumnIndex("title"))
            @SuppressLint("Range") val time = cursor.getString(cursor.getColumnIndex("time"))
            @SuppressLint("Range") val address = cursor.getString(cursor.getColumnIndex("address"))
            @SuppressLint("Range") val content = cursor.getString(cursor.getColumnIndex("content"))
            @SuppressLint("Range") val type = cursor.getString(cursor.getColumnIndex("type"))
            @SuppressLint("Range") val image = cursor.getString(cursor.getColumnIndex("image"))
            list.add(Note(id, title, time, address, content, type, image))
        }
        cursor.close()
        return list
    }

    fun queryById(key: Int): Note? {
        var note: Note? = null
        val database = this.writableDatabase
        val cursor = database.query(
            NOTE_TABLE_NAME, null,
            "id like $key", null, null, null, null
        )
        while (cursor.moveToNext()) {
            @SuppressLint("Range") val id = cursor.getInt(cursor.getColumnIndex("id"))
            @SuppressLint("Range") val title = cursor.getString(cursor.getColumnIndex("title"))
            @SuppressLint("Range") val time = cursor.getString(cursor.getColumnIndex("time"))
            @SuppressLint("Range") val address = cursor.getString(cursor.getColumnIndex("address"))
            @SuppressLint("Range") val content = cursor.getString(cursor.getColumnIndex("content"))
            @SuppressLint("Range") val type = cursor.getString(cursor.getColumnIndex("type"))
            @SuppressLint("Range") val image = cursor.getString(cursor.getColumnIndex("image"))
            note = Note(id, title, time, address, content, type, image)
        }
        cursor.close()
        return note
    }

    fun deleteById(id: Int){
        var note: Note? = null
        val database = this.writableDatabase
        database.execSQL("delete from $NOTE_TABLE_NAME where id = $id")
    }

    companion object {
        private const val NOTE_TABLE_NAME = "MyNote"
        private const val DB_NAME = "mynote.db"
        private const val V = 1
    }
}